package com.ysd.lis.controller.inv;

import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import com.ysd.common.Result;
import com.ysd.lis.controller.pub.BaseController;
import com.ysd.lis.request.inv.InvReagentLedgerParam;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.List;

/**
 * <p>
 * 前端控制器
 * </p>
 *
 * @author prs
 * @since 2024-09-21
 */
@Api(tags = "台账")
@RestController
@RequestMapping("/inv/invReagentLedger")
public class InvReagentLegderController extends BaseController {
    @Autowired
    private DataSource dataSource;

    @ApiOperation("台账查询")
    @GetMapping("/findReagentLedgerList")
    public Result findReagentStockList(InvReagentLedgerParam param) throws SQLException {
        String sql = "select a.REA_NA reaNa,a.SPEC_MODEL gg,a.unit, c.MANU_NAME as sccj, b.totalAmount,b.totalPrice from INV_REAGENT a left join (\n" +
                "select REA_NO,sum(AMOUNT) as totalAmount,sum(COSTS) as totalPrice from INV_ORDER_REAGENT a left join INV_ORDER_INFO b on a.ORDER_INFO_ID = b.id \n" +
                "where 1=1 and  FORMAT(b.DOCUMENT_DATE,'yyyy-MM') between '"+param.getStartMonth()+"'  and '"+param.getEndMonth()+"' and a.DEL_FLAG = 0 and b.DEL_FLAG = 0\n" +
                "group by REA_NO) b on a.REA_NO = b.REA_NO left join INV_MANUFACTURER c on a.MANUFACTURER_NO = c.MANU_CODE and c.del_flag = 0 \n" +
                "where a.del_flag = 0";
        List<Entity> query = Db.use(dataSource).query(sql);
        return Result.succ(1,"查询成功",query);
    }

    @ApiOperation("台账明细查询")
    @GetMapping("/findReagentLedgerListByReaNo")
    public Result findReagentLedgerListByReaNo(InvReagentLedgerParam param) throws SQLException {
        String sql = "select a.REA_NA reaNa,a.SPEC_MODEL gg,a.unit, c.MANU_NAME as sccj, b.totalAmount,b.totalPrice from INV_REAGENT a left join (\n" +
                "select REA_NO,sum(AMOUNT) as totalAmount,sum(COSTS) as totalPrice from INV_ORDER_REAGENT a left join INV_ORDER_INFO b on a.ORDER_INFO_ID = b.id \n" +
                "where 1=1 and  FORMAT(b.DOCUMENT_DATE,'yyyy-MM') between '"+param.getStartMonth()+"'  and '"+param.getEndMonth()+"' and a.DEL_FLAG = 0 and b.DEL_FLAG = 0\n" +
                "group by REA_NO) b on a.REA_NO = b.REA_NO left join INV_MANUFACTURER c on a.MANUFACTURER_NO = c.MANU_CODE and c.del_flag = 0 \n" +
                "where a.del_flag = 0";
        List<Entity> query = Db.use(dataSource).query(sql);
        return Result.succ(1,"查询成功",query);
    }


}
